package com.zsrt.oa.ticket.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zsrt.oa.ticket.dao.TicketDao;
import com.zsrt.oa.ticket.entity.TicketEntity;
import com.zsrt.utils.DBUtils;

/**
 * 门票持久层的实现类
 * @author 罗智壕-13424431416
 *
 * 2017年6月9日
 */
public class TicketDaoImpl implements TicketDao {
	
	/**
	 * 门票的增加
	 * @param entity
	 * @return
	 */
	@Override
	public int ticketAdd(TicketEntity entity) throws SQLException{
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "INSERT INTO TKT_TICKET_T (TICKET_NAME,TICKET_TIME,TICKET_PRICE) VALUES(?,sysdate(),?)";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getTicketName());
		ps.setDouble(2, entity.getTicketPrice());
		
		counter = ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		return counter;
	}
	
	/**
	 * 门票的查询
	 * @param start 从第几条开始
	 * @param end 到第几条结束
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<TicketEntity> ticketQuery(Integer start,Integer end) throws SQLException {
		List<TicketEntity> list = new ArrayList<TicketEntity>();
		Connection con = DBUtils.getMessage();
		String sql = "SELECT "
				+ " TICKET_TIME AS ticketTime,"
				+ " TICKET_ID AS ticketId,"
				+ " TICKET_NAME AS ticketName,"
				+ " TICKET_PRICE ticketPrice,"
				+ " TICKET_STATUS AS ticketStatus "
				+ " FROM TKT_TICKET_T limit ?,?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, start);
		ps.setInt(2, end);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			TicketEntity entity = new TicketEntity();
			entity.setTicketTime(rs.getString("ticketTime"));
			entity.setTicketId(rs.getInt("ticketId"));
			entity.setTicketName(rs.getString("ticketName"));
			entity.setTicketPrice(rs.getDouble("ticketPrice"));
			entity.setTicketStatus(rs.getInt("ticketStatus"));
			list.add(entity);
		}
		
		return list;
	}
	
	/**
	 * 删除门票
	 * @param ticketId
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int ticketDelete(Integer ticketId) throws SQLException {
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "delete from TKT_TICKET_T where ticket_id = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, ticketId);
		
		counter = ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		return counter;
	}
	
	/**
	 * 根据id指定查询
	 * @param ticketId
	 * @return
	 * @throws SQLException
	 */
	@Override
	public List<TicketEntity> ticketByFindId(Integer ticketId) throws SQLException {
		List<TicketEntity> list = new ArrayList<TicketEntity>();
		Connection con = DBUtils.getMessage();
		String sql = "SELECT "
				+ " TICKET_TIME AS ticketTime,"
				+ " TICKET_ID AS ticketId,"
				+ " TICKET_NAME AS ticketName,"
				+ " TICKET_PRICE ticketPrice,"
				+ " TICKET_STATUS AS ticketStatus "
				+ " FROM TKT_TICKET_T where TICKET_ID = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setInt(1, ticketId);
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			TicketEntity entity = new TicketEntity();
			entity.setTicketTime(rs.getString("ticketTime"));
			entity.setTicketId(rs.getInt("ticketId"));
			entity.setTicketName(rs.getString("ticketName"));
			entity.setTicketPrice(rs.getDouble("ticketPrice"));
			entity.setTicketStatus(rs.getInt("ticketStatus"));
			list.add(entity);
		}
		
		return list;
	}
	
	/**
	 * 门票修改
	 * @param entity
	 * @return
	 * @throws SQLException
	 */
	@Override
	public int ticketUpdate(TicketEntity entity) throws SQLException {
		int counter = 0;
		Connection con = DBUtils.getMessage();
		String sql = "update tkt_ticket_t set TICKET_NAME = ?,TICKET_PRICE = ?,TICKET_STATUS = ? where TICKET_ID = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, entity.getTicketName());
		ps.setDouble(2, entity.getTicketPrice());
		ps.setInt(3, entity.getTicketStatus());
		ps.setInt(4, entity.getTicketId());
		
		counter = ps.executeUpdate();
		
		DBUtils.closeResources(con, ps);
		return counter;
	}
}
